package com.zt.dev.physicalequipment.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author richerqiao
 * @date 2020/2/24
 */

public class ExcelUtil {

    public static HSSFWorkbook getHSSFWorkbook(HSSFWorkbook wb, String sheetName, List<String> title1,
            List<String> title2, List<List<String>> column) throws Exception {

        HSSFSheet sheet = wb.createSheet(sheetName);
        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle style = wb.createCellStyle();

        // style.setAlignment(HorizontalAlignment.CENTER);
        // style.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFFont font = wb.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);

        sheet.setColumnWidth(0, 1500);
        int c = 0;
        for (String titles : title1) {
            String[] str = titles.split(",");
            HSSFCell cell = row.createCell(c);
            if (1 != Integer.parseInt(str[2]) || 1 != Integer.parseInt(str[1])) {
                sheet.addMergedRegion(
                        new CellRangeAddress(0, Integer.parseInt(str[2]) - 1, c, c + Integer.parseInt(str[1]) - 1));
            }
            cell.setCellValue(str[0]);
            cell.setCellStyle(style);
            style.setFont(font);
            c += Integer.parseInt(str[1]);
        }

        row = sheet.createRow(1);
        char A = 65;
        for (int i = 0; i < title2.size(); i++) {
            HSSFCell cell = row.createCell(i + 1);
            cell.setCellValue(title2.get(i));
            cell.setCellStyle(style);
            sheet.setColumnWidth(i + 1, 2 * 2500);
            A++;
        }
        A = A > 90 ? 90 : A;
        if (A > 65) {
            // 筛选 A:65
            sheet.setAutoFilter(CellRangeAddress.valueOf("B2:" + A + "2"));
        }

        if (column != null && column.size() != 0) {
            for (int i = 0; i < column.size(); i++) {
                row = sheet.createRow(i + 2);
                row.createCell(0).setCellValue(i + 1);
                List<String> rowsList = column.get(i);
                for (int j = 0; j < rowsList.size(); j++) {
                    row.createCell(j + 1).setCellValue(rowsList.get(j));
                }
            }
        }
        return wb;
    }

    /**
     *      * 数据导入到excel     
     * 
     * @throws IOException
     */
    public static Map<String,List<List<String>>> importData(MultipartFile file) throws IOException {
        String exclType = file.getOriginalFilename().endsWith(".xls") ? "xls" : file.getOriginalFilename().endsWith(".xlsx") ? "xlsx" : "none";
        Workbook workbook = null;
        if ("xls".equals(exclType)) {
            workbook = new HSSFWorkbook(file.getInputStream());//得到工作簿
        } else {
            workbook = new XSSFWorkbook(file.getInputStream());//得到工作簿
        }
        Sheet sheet=null;
        Map<String,List<List<String>>> data = new HashMap<>();
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {//获取每个Sheet表
            sheet=workbook.getSheetAt(i);
            List<List<String>> sheetList = new ArrayList<>();
            String sheetName = sheet.getSheetName();
            for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {//获取每行
                Row row=sheet.getRow(j);
                List<String> rowList = new ArrayList<String>();
                for (int k = 0; k < row.getPhysicalNumberOfCells(); k++) {//获取每个单元格
                    rowList.add(String.valueOf(row.getCell(k)));
                }
                sheetList.add(rowList);
            }
            data.put(sheetName, sheetList);
        }
        if (workbook != null) {
            workbook.close();
        }
        return data;
    }

}
